/**
 * 
 */
package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;


import entity.*;
import utils.DBHelper;

/**
 * @author Chen Lei
 *
 */
public class UserDAO {
	private UserDAO() {
	}

	public static UserDAO getInstance() {
		return new UserDAO();
	}
	/**
	 * 得到系统用户总数
	 *
	 */
	public int getTotal() {
		int total = 0;
		try {

			Connection c = DBHelper.getInstance().getConnection();

			Statement s = c.createStatement();

			String sql = "select count(*) from user";

			ResultSet rs = s.executeQuery(sql);
			while (rs.next()) {
				total = rs.getInt(1);
			}

			System.out.println("total:" + total);

			DBHelper.closeConnection(c, s, rs);

		} catch (Exception e) {
			e.printStackTrace();
		}
		return total;
	}
	/**
	 * 管理员对用户进行加锁操作
	 *
	 */
	public void lockUser(User user) {
		try {

			Connection c = DBHelper.getInstance().getConnection();

			String sql = "update user set user_islocked = ? where user_id = " + user.getUser_id() ;
			
			PreparedStatement ps = c.prepareStatement(sql);
			ps.setInt(1, 1);
			
			ps.execute();
			
			DBHelper.closeConnection(c, ps, null);

		} catch (Exception e) {
			e.printStackTrace();
		}

	}
	/**
	 *  管理员对用户进行解锁操作
	 *
	 */
	public void cancelLockUser(User user) {
		try {

			Connection c = DBHelper.getInstance().getConnection();

			String sql = "update user set user_islocked = ? where user_id = " + user.getUser_id() ;
			
			PreparedStatement ps = c.prepareStatement(sql);
			ps.setInt(1, 0);
			
			ps.execute();
			
			DBHelper.closeConnection(c, ps, null);

		} catch (Exception e) {
			e.printStackTrace();
		}

	}
	/**
	 * 新增用户
	 *
	 */
	public int addUser(User user) {

		try {

			Connection c = DBHelper.getInstance().getConnection();

			String sql = "insert into user values(null,?,?,?,?,?,?)";
			PreparedStatement ps = c.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
		
			ps.setString(1, user.getUser_password());
			ps.setString(2, user.getUser_name());
			ps.setInt(3, user.getUser_sex());
			ps.setInt(4, user.getUser_islocked());
			ps.setString(5, user.getUser_email());
			ps.setString(6, user.getUser_tel());
			
			ps.execute();
			
			ResultSet rs = ps.getGeneratedKeys();
			if (rs.next()) {
				int user_id =  rs.getInt(1);
				return user_id;
				//user.setUser_id(user_id);
			}

			DBHelper.closeConnection(c, ps, rs);

		} catch (Exception e) {
			e.printStackTrace();
		}
		return -1;
	}
	/**
	 *  管理员修改用户信息
	 *
	 */
	public void updateUser(User user) {
		try {

			Connection c = DBHelper.getInstance().getConnection();

			String sql = "update user set user_password = ?,user_name = ?,user_sex = ?,user_islocked = ?, user_email = ?, user_tel = ? where user_id = " + user.getUser_id() ;
			
			PreparedStatement ps = c.prepareStatement(sql);
			ps.setString(1, user.getUser_password());
			ps.setString(2, user.getUser_name());
			ps.setInt(3, user.getUser_sex());
			ps.setInt(4, user.getUser_islocked());
			ps.setString(5, user.getUser_email());
			ps.setString(6, user.getUser_tel());
			
			ps.execute();
			
			DBHelper.closeConnection(c, ps, null);

		} catch (Exception e) {
			e.printStackTrace();
		}

	}
	/**
	 * 删除用户，删除后该用户所发的帖子发布者显示为“已注销用户”
	 *
	 */
	public void delete(int user_id) {
		try {

			Connection c = DBHelper.getInstance().getConnection();

			Statement s = c.createStatement();

			String sql = "delete from user where user_id = " + user_id;

			s.execute(sql);

			DBHelper.closeConnection(c, s, null);

		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	/**
	 * 通过id得到某用户
	 *
	 */
	public User get(int user_id) {
		User user = null;
		try {
			
			Connection c = DBHelper.getInstance().getConnection();

			Statement s = c.createStatement();

			String sql = "select * from user where user_id = " + user_id;

			ResultSet rs = s.executeQuery(sql);

			while (rs.next()) {

				user = new User();
				
				String user_name = rs.getString("user_name");
				String user_password = rs.getString("user_password");
				int user_sex = rs.getInt("user_sex");
				int user_islocked = rs.getInt("user_islocked");
				String user_email = rs.getString("user_email");
				String user_tel = rs.getString("user_tel");
				
				user.setUser_id(user_id);
				user.setUser_name(user_name);
				user.setUser_password(user_password);
				user.setUser_sex(user_sex);
				user.setUser_islocked(user_islocked);
				user.setUser_email(user_email);
				user.setUser_tel(user_tel);
				
			}

			DBHelper.closeConnection(c, s, rs);

		} catch (Exception e) {
			e.printStackTrace();
		}
		return user;
	}
	/**
	 * 得到论坛所有用户信息
	 *
	 */
	public List<User> list() {
		return list(0, Short.MAX_VALUE);
	}
	/**
	 * 得到论坛从start开始count个用户信息
	 *
	 */
	public List<User> list(int start, int count) {
		List<User> users = new ArrayList<User>();

		try {

			Connection c = DBHelper.getInstance().getConnection();

			String sql = "select * from user ORDER BY user_id DESC limit ?,? ";

			PreparedStatement ps = c.prepareStatement(sql);
			ps.setInt(1, start);
			ps.setInt(2, count);

			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				User user = new User();
				
				int user_id = rs.getInt("user_id");
				String user_name = rs.getString("user_name");
				String user_password = rs.getString("user_password");
				int user_sex = rs.getInt("user_sex");
				int user_islocked = rs.getInt("user_islocked");
				String user_email = rs.getString("user_email");
				String user_tel = rs.getString("user_tel");
				
				user.setUser_id(user_id);
				user.setUser_name(user_name);
				user.setUser_password(user_password);
				user.setUser_sex(user_sex);
				user.setUser_islocked(user_islocked);
				user.setUser_email(user_email);
				user.setUser_tel(user_tel);
				
				users.add(user);
			}
			DBHelper.closeConnection(c, ps, rs);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return users;
	}
	
}
